using System;
using System.Collections.Generic;
using CMS.Core.DataInterfaces;
using CMS.Core.Domain;
using NHibernate.Criterion;
using NHibernate;
using System.Text.RegularExpressions;

namespace CMS.Data
{
    public class BaiVietDao : AbstractNHibernateDao<BaiViet, System.Int32>, IBaiVietDao
    {
        #region Create Instance
        private static BaiVietDao instance = null;
        public static BaiVietDao Instance
        {
            get
            {
                if (instance == null)
                {
                    instance = new BaiVietDao();
                }
                return instance;
            }
        }
        #endregion

        #region IBaiVietDao Members

        public IList<BaiViet> GetAllBaiVietWithChucNang(int MaChucNang)
        {
            try
            {
                String strQuery = @"Select bv From BaiVietTheLoai tl Inner join tl.BaiViets bv Where tl.ChucNangLookup.ID = :MACHUCNANG Order by bv.NgayCapNhat DESC, bv.ID DESC";
                List<BaiViet> Result = NHibernateSessionManager.Instance.GetSession().CreateQuery(strQuery)
                        .SetParameter("MACHUCNANG", MaChucNang)
                        .List<BaiViet>() as List<BaiViet>;
                return Result;
            }
            catch (Exception ex)
            {
                return new List<BaiViet>();
            }
        }

        public IList<BaiVietTheLoai> GetAllTheLoaiWithChucNang(int MaChucNang)
        {
            try
            {
                String strQuery = @"From BaiVietTheLoai tl Where tl.ChucNangLookup.ID = :MACHUCNANG AND tl.TheLoaiCha IS NOT NULL Order By tl.TenTheLoai";
                List<BaiVietTheLoai> Result = NHibernateSessionManager.Instance.GetSession().CreateQuery(strQuery)
                        .SetParameter("MACHUCNANG", MaChucNang)
                        .List<BaiVietTheLoai>() as List<BaiVietTheLoai>;
                return Result;
            }
            catch (Exception ex)
            {
                return new List<BaiVietTheLoai>();
            }
        }

        public IList<BaiViet> GetAllBaiVietOtherBaiViet(int MaTheLoai, int MaBaiViet)
        {
            try
            {
                String strSQL = String.Empty;
                List<BaiViet> results;
                List<BaiViet> results1;
                strSQL = "From BaiViet bv Where bv.MaTheLoaiLookup.ID =:MATL AND bv.TrangThai =:TRANGTHAI AND bv.ID < :MABV Order by bv.ID Desc";
                IQuery q1 = NHibernateSessionManager.Instance.GetSession().CreateQuery(strSQL);
                q1.SetInt32("MABV", MaBaiViet);
                q1.SetInt32("MATL", MaTheLoai);
                q1.SetBoolean("TRANGTHAI", true);
                q1.SetFirstResult(0);
                q1.SetMaxResults(5);
                results = q1.List<BaiViet>() as List<BaiViet>;

                strSQL = "From BaiViet bv Where bv.MaTheLoaiLookup.ID =:MATL AND bv.TrangThai =:TRANGTHAI AND bv.ID > :MABV Order by bv.ID Asc";
                IQuery q2 = NHibernateSessionManager.Instance.GetSession().CreateQuery(strSQL);
                q2.SetInt32("MABV", MaBaiViet);
                q2.SetInt32("MATL", MaTheLoai);
                q2.SetBoolean("TRANGTHAI", true);
                q2.SetFirstResult(0);
                q2.SetMaxResults(5);
                results1 = q2.List<BaiViet>() as List<BaiViet>;
                foreach (BaiViet entity in results1)
                {
                    results.Add(entity);
                }
                results.Sort(delegate(BaiViet a1, BaiViet a2) { return a2.ID.CompareTo(a1.ID); });
                return results;                
            }
            catch (Exception ex)
            {
                return new List<BaiViet>();
            }
        }
        
        public IList<BaiViet> GetNewsTop5()
        {
            IList<BaiViet> results;
            String strSQL = "";
            strSQL = @"FROM BaiViet BV WHERE BV.TrangThai=:trangthai AND BV.MaTheLoaiLookup.ID IN (SELECT TL.ID FROM BaiVietTheLoai TL WHERE TL.ChucNangLookup.ID=:maCN) Order By BV.NgayCapNhat DESC,BV.ID DESC";
            IQuery q = NHibernateSessionManager.Instance.GetSession().CreateQuery(strSQL);
            q.SetBoolean("trangthai", true);
            q.SetInt32("maCN", CauHinhHeThong.MaTinTuc);
            q.SetMaxResults(6);
            results = q.List<BaiViet>() as List<BaiViet>;
            return results;
        }

        public Int32 GetCountByTheLoai(Int32 maTheLoai)
        {
            List<BaiViet> results;
            ICriteria criteria = NHibernateSessionManager.Instance.GetSession().CreateCriteria(typeof(BaiViet));
            criteria.SetMaxResults(2);
            criteria.Add(Restrictions.Eq("MaTheLoaiLookup.ID", maTheLoai));            
            criteria.Add(Restrictions.Eq("TrangThai", true));            
            results = criteria.List<BaiViet>() as List<BaiViet>;
            if ((results != null) && (results.Count == 1))
            {
                return results[0].ID;
            }
            return -1;
        }

        public IList<BaiViet> FilterBaiViets(int MaChucNang, int MaTheLoai, string TieuDe, string NguonTin, 
                                string TrangThai, string StartDate, String EndDate)
        {
            IList<BaiViet> result = new List<BaiViet>();  
            DateTime Ngay1 = DateTime.MinValue;
            DateTime Ngay2 = DateTime.MinValue;
            bool state = true;

            try
            {
                String strSQL = @"FROM BaiViet bv " +
                                 "WHERE (bv.MaTheLoaiLookup.ID IN (SELECT tl.ID FROM BaiVietTheLoai tl WHERE tl.ChucNangLookup.ID = :MACHUCNANG)) ";                
                String strCondition = "";
                if (MaTheLoai != 0)
                {
                    strCondition += "AND" + " (bv.MaTheLoaiLookup.ID = :MATL) ";
                }                

                if (!TieuDe.Equals(String.Empty))
                {                    
                    strCondition += "AND" + " (bv.TieuDe LIKE :TIEUDE) ";
                }

                if (!NguonTin.Equals(String.Empty))
                {                    
                    strCondition += "AND" + " (bv.NguonTin LIKE :NGUONTIN) ";
                }

                if (!TrangThai.Equals(String.Empty))
                {
                    state = TrangThai.Equals("1") ? true : false;
                    strCondition += "AND" + " (bv.TrangThai = :TRANGTHAI) ";
                }                

                if (!StartDate.Equals(""))
                {
                    string[] temp = StartDate.Split(new Char[] { '/' });
                    Ngay1 = new DateTime(Convert.ToInt16(temp[2]), Convert.ToInt16(temp[1]), Convert.ToInt16(temp[0]), 0, 0, 0, 0);
                    strCondition += "AND" + " (bv.NgayCapNhat >= :NGAY1) ";
                }

                if (!EndDate.Equals(""))
                {
                    string[] temp = EndDate.Split(new Char[] { '/' });
                    Ngay2 = new DateTime(Convert.ToInt16(temp[2]), Convert.ToInt16(temp[1]), Convert.ToInt16(temp[0]), 0, 0, 0, 0);
                    strCondition += "AND" + " (bv.NgayCapNhat <= :NGAY2) ";
                }

                strSQL += strCondition + " Order by bv.NgayCapNhat DESC, bv.ID DESC";

                IQuery q = NHibernateSessionManager.Instance.GetSession().CreateQuery(strSQL);

                q.SetInt32("MACHUCNANG", MaChucNang);

                if (MaTheLoai != 0)
                {
                    q.SetInt32("MATL", MaTheLoai);
                }               

                if (!TieuDe.Equals(String.Empty))
                {
                    q.SetString("TIEUDE", "%" + TieuDe + "%");
                }

                if (!NguonTin.Equals(String.Empty))
                {
                    q.SetString("NGUONTIN", "%" + NguonTin + "%");
                }

                if (!TrangThai.Equals(String.Empty))
                {
                    q.SetBoolean("TRANGTHAI", state);
                }               

                if (!StartDate.Equals(""))
                {
                    q.SetDateTime("NGAY1", Ngay1);
                }

                if (!EndDate.Equals(""))
                {
                    q.SetDateTime("NGAY2", Ngay2);
                }
                
                result = q.List<BaiViet>();
            }
            catch (Exception ex)
            {
                string str = ex.ToString();
            }

            return result;
        }

        #endregion
    }
}
